"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : GSC功能模块
Case Name   : GSC相关系统函数用户访问权限验证
Create At   : 2022/4/11
Owner       : opentestcase024
Description :
    1、创建系统管理员用户和普通用户
    2、普通用户查询GSC相关函数
    3、系统用户查询GSC相关函数
Expect      :
    1、创建系统管理员用户和普通用户; 创建成功
    2、普通用户查询GSC相关函数;
    2.1、查询gs_gsc_memory_detail; 查询正常
    2.2、查询gs_lsc_memory_detail; 查询正常
    2.3、查询gs_gsc_dbstat_info; 无权限
    2.4、查询gs_gsc_table_detail; 无权限
    2.5、查询gs_gsc_catalog_detail; 无权限
    2.6、查询gs_gsc_clean; 无权限
    3、系统用户查询GSC相关函数;
    3.1、查询gs_gsc_memory_detail; 查询正常
    3.2、查询gs_lsc_memory_detail; 查询正常
    3.3、查询gs_gsc_dbstat_info; 查询正常
    3.4、查询gs_gsc_table_detail; 查询正常
    3.5、查询gs_gsc_catalog_detail; 查询正常
    3.6、查询gs_gsc_clean; 查询正常
History     :
"""

import os
import re
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import macro


class GscTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.log.info("----this is setup----")
        self.sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.pwd = macro.PASSWD_INITIAL
        self.common_user = 'u_gsc0037_1'
        self.sys_user = 'u_gsc0037_2'
        self.con_com = f'-U {self.common_user} -W {self.pwd}'
        self.con_sys = f'-U {self.sys_user} -W {self.pwd}'
        self.err_msg = 'ERROR:  Fail to .*.\n' \
                       'DETAIL:  Insufficient privilege to .*.'

    def test_main(self):
        step_txt = '----step1:创建系统管理员用户和普通用户; expect:创建成功----'
        self.log.info(step_txt)
        create_sql = f"drop user if exists {self.common_user} cascade; " \
            f"drop user if exists {self.sys_user} cascade; " \
            f"create user {self.common_user} password '{self.pwd}';" \
            f"create user {self.sys_user} sysadmin password '{self.pwd}';"
        create_result = self.sh.execut_db_sql(create_sql)
        self.log.info(create_result)
        assert_flag = create_result.splitlines().count(
            self.constant.CREATE_ROLE_SUCCESS_MSG)
        self.assertEqual(assert_flag, 2, "执行失败" + step_txt)

        step_txt = '----step2:普通用户查询GSC相关函数;----'
        self.log.info(step_txt)
        step_txt = '----step2.1:查询gs_gsc_memory_detail; expect:查询正常----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_memory_detail',
                                          self.con_com)
        self.assertIn('1 row', select_result, '执行失败:' + step_txt)

        step_txt = '----step2.2:查询gs_lsc_memory_detail; expect:查询正常----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_lsc_memory_detail',
                                          self.con_com)
        self.assertIn('1 row', select_result, '执行失败:' + step_txt)

        step_txt = '----step2.3:查询gs_gsc_dbstat_info; expect:无权限----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_dbstat_info()',
                                          self.con_com)
        self.assertGreater(len(re.findall(self.err_msg, select_result)), 0,
                           '执行失败:' + step_txt)

        step_txt = '----step2.4:查询gs_gsc_table_detail; expect:无权限----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_table_detail()',
                                          self.con_com)
        self.assertGreater(len(re.findall(self.err_msg, select_result)), 0,
                           '执行失败:' + step_txt)

        step_txt = '----step2.5:查询gs_gsc_catalog_detail; expect:无权限----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_catalog_detail()',
                                          self.con_com)
        self.assertGreater(len(re.findall(self.err_msg, select_result)), 0,
                           '执行失败:' + step_txt)

        step_txt = '----step2.6:查询gs_gsc_clean; expect:无权限----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_clean()', self.con_com)
        self.assertGreater(len(re.findall(self.err_msg, select_result)), 0,
                           '执行失败:' + step_txt)

        step_txt = '----step3:系统用户查询GSC相关函数;----'
        self.log.info(step_txt)
        step_txt = '----step3.1:查询gs_gsc_memory_detail; expect:查询正常----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_memory_detail',
                                          self.con_sys)
        self.assertIn('1 row', select_result, '执行失败:' + step_txt)

        step_txt = '----step3.2:查询gs_lsc_memory_detail; expect:查询正常----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_lsc_memory_detail',
                                          self.con_sys)
        self.assertIn('1 row', select_result, '执行失败:' + step_txt)

        step_txt = '----step3.3:查询gs_gsc_dbstat_info; expect:查询正常----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_dbstat_info()',
                                          self.con_sys)
        self.assertIn('1 row', select_result, '执行失败:' + step_txt)

        step_txt = '----step3.4:查询gs_gsc_table_detail; expect:查询正常----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_table_detail()',
                                          self.con_sys)
        self.assertIn('1 row', select_result, '执行失败:' + step_txt)

        step_txt = '----step3.5:查询gs_gsc_catalog_detail; expect:查询正常----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_catalog_detail()',
                                          self.con_sys)
        self.assertIn('1 row', select_result, '执行失败:' + step_txt)

        step_txt = '----step3.6:查询gs_gsc_clean; expect:查询正常----'
        self.log.info(step_txt)
        select_result = self.check_select('gs_gsc_clean()', self.con_sys)
        self.assertIn('1 row', select_result, '执行失败:' + step_txt)

    def check_select(self, object_name, con_user):
        """
        :param object_name: 查询对象名称
        :param con_user: 查询结果
        :return: 
        """
        select_sql1 = f'select count(*) from {object_name};'
        select_result = self.sh.execut_db_sql(select_sql1,
                                              sql_type=con_user)
        self.log.info(select_result)
        return select_result

    def tearDown(self):
        self.log.info('----this is teardown----')
        step_txt = '----删除用户; expect:成功----'
        self.log.info(step_txt)
        clean_sql = f"drop user if exists {self.common_user} cascade; " \
            f"drop user if exists {self.sys_user} cascade; "
        clean_result = self.sh.execut_db_sql(clean_sql)
        self.log.info(clean_result)

        self.log.info(f'-----{os.path.basename(__file__)} end-----')

        drop_count = clean_result.count(self.constant.DROP_ROLE_SUCCESS_MSG)
        self.assertEqual(drop_count, 2, "执行失败" + step_txt)
